﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Frunit.Model;
using OMG.Fill.Frunit.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;

namespace OMG.Fill.Frunit.Services
{
    /// <summary>
    /// 表3生产情况调查表T_FruProSurvey通用操作类
    /// </summary>
    public class FruProSurveyServices : FruProSurveys
    {
        public Pager<List<FruProSurveyEntity>> GetPageList(FruProSurveyQuery query)
        {
            Pager<List<FruProSurveyEntity>> obj = new Pager<List<FruProSurveyEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_FruProSurvey where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<FruProSurveyEntity>(strSql, paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal, paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取表3生产情况调查表T_FruProSurvey分页列表失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 判定是否生成过报表
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns>没生成过true,否则false</returns>
        public bool CheckRepCount(string reportId)
        {
            string strSql = @"select count(1) from dbo.T_FruProSurvey where ReportId=@ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportId = reportId }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("更新表3生产情况调查表T_FruProSurvey对象信息失败", ex);
                return false;
            }
        }
        /// <summary>
        /// 获取填报数据
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns></returns>
        public ResResult<List<FruProSurveyEntity>> GetInfo(string reportId)
        {
            ResResult<List<FruProSurveyEntity>> obj = new ResResult<List<FruProSurveyEntity>>();
            //判定是否生成过报表
            if (CheckRepCount(reportId))
            {
                //获取报表内容(报编号，查询状态:0为初始化状态,1为已被初始化从初始化表读列表)
                List<FruProSurveyEntity> templist = GetTabContent(reportId, 0);
                try
                {
                    if (AddInfo(templist) > 0)
                    {
                        obj.model = templist;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "初始化并且返回数据成功！";
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("新增表3生产情况调查表T_FruProSurvey对象信息失败", ex);
                    obj.message = "返回数据成功！";
                }
            }
            else
            {
                try
                {
                    //获取报表内容(报编号，查询状态:0为初始化状态,1为已被初始化从初始化表读列表)
                    List<FruProSurveyEntity> templist = GetTabContent(reportId, 1);
                    obj.model = templist;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "返回数据成功！";
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("新增表3生产情况调查表T_FruProSurvey对象信息失败", ex);
                    obj.message = "返回数据成功！";
                }
            }
            return obj;
        }
        /// <summary>
        /// 获取填报数据
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns></returns>
        public ResResult<List<FruProSurveyEntity>> GetDataInfo(string reportId)
        {
            ResResult<List<FruProSurveyEntity>> obj = new ResResult<List<FruProSurveyEntity>>();
            //判定是否生成过报表
            if (CheckRepCount(reportId))
            {
                //获取报表内容(报编号，查询状态:0为初始化状态,1为已被初始化从初始化表读列表)
                List<FruProSurveyEntity> templist = GetContent(reportId);
                try
                {
                    if (AddInfo(templist) > 0)
                    {
                        obj.model = templist;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "初始化并且返回数据成功！";
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("新增表3生产情况调查表T_FruProSurvey对象信息失败", ex);
                    obj.message = "返回数据成功！";
                }
            }
            else
            {
                try
                {
                    string strSql = @"select * from dbo.T_FruProSurvey where ReportId = @ReportId order by SortNum";
                    using (var dbCon = ConnectFactory.GetOpenConnection())
                    {
                        List<FruProSurveyEntity> templist = dbCon.Query<FruProSurveyEntity>(strSql, new { ReportId = reportId }).ToList();
                        obj.model = templist;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "返回数据成功！";
                    }

                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("新增表3生产情况调查表T_FruProSurvey对象信息失败", ex);
                    obj.message = "返回数据成功！";
                }
            }
            return obj;
        }
        /// <summary>
        /// 用于获取主要水果生产情况表的种类
        /// </summary>
        /// <returns></returns>
        public List<FruProSurveyEntity> GetContent(string reportId)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            List<FruProSurveyEntity> list = new List<FruProSurveyEntity>();
            string pathName = HttpRuntime.AppDomainAppPath.ToString() + @"Excel\FruProSurvey.xls";
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            workbook = new HSSFWorkbook(fs);
            sheet = workbook.GetSheetAt(0);
            int startRow = 3;
            if (sheet != null)
            {
                //获取有数据的行数
                int rowCount = 0;
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        rowCount = sheet.GetRow(i).RowNum;
                    }
                    else
                    {
                        break;
                    }
                }
                int sortNum = 0;
                for (int i = startRow; i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    FruProSurveyEntity entity = new FruProSurveyEntity();
                    entity.ReportId = reportId;
                    entity.ParentCode = row.GetCell(0).ToString();
                    entity.ParentName = row.GetCell(1).ToString();
                    entity.VarietyCode = row.GetCell(2).ToString();
                    entity.Variety = row.GetCell(3).ToString();
                    entity.SortNum = sortNum++;
                    list.Add(entity);
                }
            }
            return list;
        }

        /// <summary>
        /// 初始化报表内容
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public int AddInfo(List<FruProSurveyEntity> list)
        {
            string strSql = @"INSERT INTO dbo.T_FruProSurvey";
            strSql += "(";
            strSql += "Id,";
            strSql += "ReportId,";
            strSql += "SortCode,";
            strSql += "SortName,";
            strSql += "SortSonCode,";
            strSql += "SortSonName,";
            strSql += "ItemCode,";
            strSql += "ItemName,";
            strSql += "GrandArea,";
            strSql += "NewArea,";
            strSql += "FOrchard,";
            strSql += "FTOrchard,";
            strSql += "TOrchard,";
            strSql += "CumYield,";
            strSql += "AvgMuYield,";
            strSql += "Remark,";
            strSql += "Variety,";
            strSql += "ParentCode,";
            strSql += "ParentName,";
            strSql += "VarietyCode,";
            strSql += "SortNum,";
            strSql += "AvgPrice)";
            strSql += " values(";
            strSql += "@Id,";
            strSql += "@ReportId,";
            strSql += "@SortCode,";
            strSql += "@SortName,";
            strSql += "@SortSonCode,";
            strSql += "@SortSonName,";
            strSql += "@ItemCode,";
            strSql += "@ItemName,";
            strSql += "@GrandArea,";
            strSql += "@NewArea,";
            strSql += "@FOrchard,";
            strSql += "@FTOrchard,";
            strSql += "@TOrchard,";
            strSql += "@CumYield,";
            strSql += "@AvgMuYield,";
            strSql += "@Remark,";
            strSql += "@Variety,";
            strSql += "@ParentCode,";
            strSql += "@ParentName,";
            strSql += "@VarietyCode,";
            strSql += "@SortNum,";
            strSql += "@AvgPrice)";
            using (var dbCon = ConnectFactory.GetOpenConnection())
            {
                try
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        list[i].Id = CustomFun.GetGuid();
                    }
                    int result = dbCon.Execute(strSql, list);
                    dbCon.Dispose();//数据库连接释放
                    return DefaultKey.SuccessResult;
                }
                catch (Exception ex)
                {
                    LogHelper.ErrorLog("新增表3生产情况调查表T_FruProSurvey对象信息失败", ex);
                    dbCon.Dispose();//数据库连接释放
                    return DefaultKey.ErrorResult;

                }
            }
        }
        public ResResult<int> UpdateInfo(List<FruProSurveyEntity> list)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"UPDATE dbo.T_FruProSurvey set ";
            strSql += "ReportId = @ReportId,";
            strSql += "SortCode = @SortCode,";
            strSql += "SortName = @SortName,";
            strSql += "SortSonCode = @SortSonCode,";
            strSql += "SortSonName = @SortSonName,";
            strSql += "ItemCode = @ItemCode,";
            strSql += "ItemName = @ItemName,";
            strSql += "GrandArea = @GrandArea,";
            strSql += "NewArea = @NewArea,";
            strSql += "FOrchard = @FOrchard,";
            strSql += "FTOrchard = @FTOrchard,";
            strSql += "TOrchard = @TOrchard,";
            strSql += "CumYield = @CumYield,";
            strSql += "AvgPrice = @AvgPrice,";
            strSql += "Remark = @Remark,";
            strSql += "AvgMuYield = @AvgMuYield";
            strSql += " where Id = @Id";
            using (var dbCon = ConnectFactory.GetOpenConnection())
            {
                var trans = dbCon.BeginTransaction();
                try
                {
                    if (list.Count > 0)
                    {
                        string strRep = "update dbo.T_Report set Status='2',UpdateTime=@UpdateTime where Id=@ReportId";
                        dbCon.Execute(strRep, new { ReportId = list[0].ReportId, UpdateTime = DateTime.Now }, trans);
                    }
                    int result = dbCon.Execute(strSql, list, trans);
                    //提交事务
                    trans.Commit();
                    obj.model = result;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "更新成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;

                }
                catch (Exception ex)
                {
                    //出现异常，事务Rollback
                    trans.Rollback();
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("更新表3生产情况调查表T_FruProsecution对象信息失败", ex);
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
        }
        /// <summary>
        /// 获取报表内容
        /// </summary>
        /// <param name="reportId"></param>
        /// <param name="edittype">0的时候返回视图列表，不为0的时候返回数据库列表</param>
        /// <returns></returns>
        public List<FruProSurveyEntity> GetTabContent(string reportId, int edittype)
        {
            List<FruProSurveyEntity> obj = new List<FruProSurveyEntity>();
            string strSql = "";
            if (edittype == 0)
            {
                strSql = @"select * from dbo.V_FruProSurvey order by SortCode asc,ItemCode asc";
            }
            else
            {
                strSql = @"select * from dbo.T_FruProSurvey where ReportId='" + reportId + "' order by SortCode asc,ItemCode asc";
            }
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj = dbCon.Query<FruProSurveyEntity>(strSql).ToList();
                    if (edittype == 0)
                    {
                        foreach (var item in obj)
                        {
                            item.ReportId = reportId;
                            item.Id = CustomFun.GetGuid();
                        }
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("更新表3生产情况调查表T_FruProSurvey对象信息失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 读取导入的Excel数据
        /// </summary>
        /// <param name="pathName">导入文件路径</param>
        /// <param name="fileSuffix">文件后缀</param>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="startRow">从第几行开始读取数据</param>
        /// <returns></returns>
        public List<FruProSurveyEntity> ExcelToModel(string pathName, string fileSuffix, string sheetName, int startRow)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            List<FruProSurveyEntity> list = new List<FruProSurveyEntity>();
            try
            {

                FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
                switch (fileSuffix)
                {
                    case ".xls":
                        workbook = new HSSFWorkbook(fs);
                        break;
                    case ".xlsx":
                        workbook = new XSSFWorkbook(fs);
                        break;
                    default:
                        workbook = new HSSFWorkbook(fs);
                        break;
                }
                if (!string.IsNullOrEmpty(sheetName))
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(2);//从第四行开始读
                    int cellCount = firstRow.LastCellNum;//获取列数

                    //获取所有数据的行数
                    int rowCount = 0;
                    for (int i = startRow; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row != null)
                        {
                            rowCount = sheet.GetRow(i).RowNum;
                        }
                        else
                        {
                            break;
                        }
                    }
                    for (int i = startRow; i <= rowCount; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        FruProSurveyEntity model = new FruProSurveyEntity();
                        //model.SortCode = row.GetCell(0).ToString();
                        //model.SortName = row.GetCell(1).ToString();
                        //model.ItemCode = row.GetCell(2).ToString();
                        //model.ItemName = row.GetCell(3).ToString();
                        model.Variety = row.GetCell(0).ToString();
                        //取全年累计面积
                        //if (row.GetCell(1) != null)
                        //{
                        //    if (row.GetCell(1).CellType == CellType.Formula)
                        //    {
                        //        model.GrandArea = Decimal.Round(Convert.ToDecimal(row.GetCell(1).NumericCellValue), 1);
                        //    }
                        //    else
                        //    {
                        //        if (DataCheck.isNumber(row.GetCell(1).ToString()))
                        //        {
                        //            model.GrandArea = Convert.ToDecimal(row.GetCell(1).ToString());//全年累计面积
                        //        }
                        //        else
                        //        {
                        //            model.GrandArea = null;
                        //        }
                        //    }
                        //}
                       


                        if (row.GetCell(2) != null )
                        {
                            if (row.GetCell(2).CellType == CellType.Formula)
                            {
                                model.CumYield = Decimal.Round(Convert.ToDecimal(row.GetCell(2).NumericCellValue), 1);//全年累计产量
                            }
                            else
                            {
                                if (DataCheck.isNumber(row.GetCell(2).ToString()))
                                {
                                    model.CumYield = Convert.ToDecimal(row.GetCell(2).ToString());//全年累计产量
                                }
                                else
                                {
                                    model.CumYield = null;
                                }
                            }
                        }
                       

                        if (model.GrandArea != null &&model.GrandArea != 0&& model.CumYield != null)
                        {
                            model.AvgMuYield = Decimal.Round(Convert.ToDecimal((model.CumYield / model.GrandArea).ToString()), 1);
                        }
                        /*  if (row.GetCell(3).CellType == CellType.Formula)
                          {
                              model.AvgMuYield = Decimal.Round(Convert.ToDecimal(row.GetCell(3).NumericCellValue), 1);//平均亩产
                          }
                          else
                          {
                              if (DataCheck.isNumber(row.GetCell(3).ToString()))
                              {
                                  model.AvgMuYield = Convert.ToDecimal(row.GetCell(3).ToString());//平均亩产
                              }
                              else
                              {
                                  model.AvgMuYield = null;
                              }
                          }*/


                        if (row.GetCell(4) != null )
                        {
                            if (row.GetCell(4).CellType == CellType.Formula)
                            {
                                model.NewArea = Decimal.Round(Convert.ToDecimal(row.GetCell(4).NumericCellValue), 1);//当年新建果园
                            }
                            else
                            {
                                if (DataCheck.isNumber(row.GetCell(4).ToString()))
                                {
                                    model.NewArea = Convert.ToDecimal(row.GetCell(4).ToString());//当年新建果园
                                }
                                else
                                {
                                    model.NewArea = null;
                                }
                            }
                        }
                       



                        if (!model.Variety.Equals("西瓜") || !model.Variety.Equals("甜瓜") || !model.Variety.Equals("草莓") || !model.Variety.Equals("人参果"))
                        {
                            if (row.GetCell(5) != null  )
                            {
                                if (row.GetCell(5).CellType == CellType.Formula)
                                {
                                    model.FOrchard = Decimal.Round(Convert.ToDecimal(row.GetCell(5).NumericCellValue), 1);//五年以下果园
                                }
                                else
                                {
                                    if (DataCheck.isNumber(row.GetCell(5).ToString()))
                                    {
                                        model.FOrchard = Convert.ToDecimal(row.GetCell(5).ToString());//五年以下果园
                                    }
                                    else
                                    {
                                        model.FOrchard = null;
                                    }
                                }
                            }
                           
                        }

                        if (!model.Variety.Equals("西瓜") || !model.Variety.Equals("甜瓜") || !model.Variety.Equals("草莓") || !model.Variety.Equals("人参果"))
                        {
                            if (row.GetCell(6) != null)
                            {
                                if (row.GetCell(6).CellType == CellType.Formula)
                                {
                                    model.FTOrchard = Decimal.Round(Convert.ToDecimal(row.GetCell(6).NumericCellValue), 1);//5-20年果园
                                }
                                else
                                {
                                    if (DataCheck.isNumber(row.GetCell(6).ToString()))
                                    {
                                        model.FTOrchard = Convert.ToDecimal(row.GetCell(6).ToString());//5-20年果园
                                    }
                                    else
                                    {
                                        model.FTOrchard = null;
                                    }
                                }
                            }
                           
                        }

                        if (!model.Variety.Equals("西瓜") || !model.Variety.Equals("甜瓜") || !model.Variety.Equals("草莓") || !model.Variety.Equals("人参果"))
                        {
                            if (row.GetCell(7) != null  )
                            {
                                if (row.GetCell(7).CellType == CellType.Formula)
                                {
                                    model.TOrchard = Decimal.Round(Convert.ToDecimal(row.GetCell(7).NumericCellValue), 1);//20年以上果园
                                }
                                else
                                {
                                    if (DataCheck.isNumber(row.GetCell(7).ToString()))
                                    {
                                        model.TOrchard = Convert.ToDecimal(row.GetCell(7).ToString());//20年以上果园
                                    }
                                    else
                                    {
                                        model.TOrchard = null;
                                    }
                                }
                            }
                            
                        }
                        model.Remark = row.GetCell(8).ToString();//备注
                        //计算全年累计面积
                        decimal tempGrand = 0;
                        if(model.NewArea != null)
                        {
                            tempGrand += (decimal)model.NewArea;
                        }
                        if (model.FOrchard != null)
                        {
                            tempGrand += (decimal)model.FOrchard;
                        }
                        if (model.FTOrchard != null)
                        {
                            tempGrand += (decimal)model.FTOrchard;
                        }
                        if (model.TOrchard != null)
                        {
                            tempGrand += (decimal)model.TOrchard;
                        }
                        if(tempGrand != 0)
                        {
                            model.GrandArea = tempGrand;
                        }                       
                        list.Add(model);
                    }
                }
                if(list.Count != 0)
                {
                    FruProSurveyEntity entity = list[list.Count - 1];
                    entity.GrandArea = 0;
                    entity.CumYield = 0;
                    entity.NewArea = 0;
                    entity.FOrchard = 0;
                    entity.FTOrchard = 0;
                    entity.TOrchard = 0;
                    
                    for (int r = 0; r < list.Count - 1; r++)
                    {
                        if (r < 3 || r > 8)//非子项加和
                        {
                            if(list[r].GrandArea != null)
                            entity.GrandArea += list[r].GrandArea;
                            if (list[r].CumYield != null)
                                entity.CumYield += list[r].CumYield;
                            if (list[r].NewArea != null)
                                entity.NewArea += list[r].NewArea;
                            if (list[r].FOrchard != null)
                                entity.FOrchard += list[r].FOrchard;
                            if (list[r].FTOrchard != null)
                                entity.FTOrchard += list[r].FTOrchard;
                            if (list[r].TOrchard != null)
                                entity.TOrchard += list[r].TOrchard;
                        }                      
                    }
                    if(entity.GrandArea != 0)
                    {
                        decimal temp = (decimal)(entity.CumYield / entity.GrandArea);
                        entity.AvgMuYield = Decimal.Round(temp,1);
                    }
                    list[list.Count - 1] = entity;                   
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex.ToString().Trim());
            }
            return list;
        }
        public ResResult<List<ProSurveyStatisModel>> getStatisListData(string AreaCode, string FillInYear, string VarietyCode)
        {
            ResResult<List<ProSurveyStatisModel>> obj = new ResResult<List<ProSurveyStatisModel>>();
            List<ProSurveyStatisModel> list = new List<ProSurveyStatisModel>();
            AreaServices areaServices = new AreaServices();
            if (AreaCode.Equals("530000000000"))
            {
                var citylist = areaServices.GetPrefectureList();
                ProSurveyStatisModel totalModel = new ProSurveyStatisModel();
                totalModel.AreaName = "合计";
                totalModel.FOrchard = 0;
                totalModel.FTOrchard = 0;
                totalModel.GrandArea = 0;
                totalModel.NewArea = 0;
                totalModel.TOrchard = 0;
                totalModel.CumYield = 0;
                //totalModel.tag = 2;
                foreach (var code in citylist)
                {
                    ProSurveyStatisModel cityModel = getProvinStatis(code, FillInYear, VarietyCode);
                    if (cityModel != null)
                    {
                        cityModel.AreaName = areaServices.GetByCodeModel(code).model.Name;
                        if (cityModel.GrandArea != 0&&cityModel.CumYield != null)
                        {
                            cityModel.AvgMuYield = Decimal.Round((decimal)(cityModel.CumYield / cityModel.GrandArea),1);
                        }
                        totalModel.FOrchard += cityModel.FOrchard;
                        totalModel.FTOrchard += cityModel.FTOrchard;
                        totalModel.GrandArea += cityModel.GrandArea;
                        totalModel.NewArea += cityModel.NewArea;
                        totalModel.TOrchard += cityModel.TOrchard;
                        totalModel.CumYield += cityModel.CumYield;
                    }
                    else
                    {
                        cityModel = new ProSurveyStatisModel();
                        cityModel.AreaName = areaServices.GetByCodeModel(code).model.Name;
                        cityModel.FOrchard = 0;
                        cityModel.FTOrchard = 0;
                        cityModel.GrandArea = 0;
                        cityModel.NewArea = 0;
                        cityModel.TOrchard = 0;
                        cityModel.CumYield = 0;
                    }
                    //cityModel.tag = 1;
                    list.Add(cityModel);

                }
                if (totalModel.GrandArea != 0&&totalModel.CumYield != null)
                {
                    totalModel.AvgMuYield = Decimal.Round((decimal)(totalModel.CumYield / totalModel.GrandArea),1);
                }
                list.Add(totalModel);
            }
            else
            {
                if (AreaCode.Substring(4, 8).Equals("00000000"))
                {
                    list = getCityStatisList(AreaCode, FillInYear, VarietyCode);
                    ProSurveyStatisModel cityModel = getProvinStatis(AreaCode, FillInYear, VarietyCode);
                    if (cityModel != null)
                    {
                        if (cityModel.GrandArea != 0&&cityModel.CumYield != null)
                        {
                            cityModel.AvgMuYield = Decimal.Round((decimal)(cityModel.CumYield / cityModel.GrandArea), 1);
                        }
                    }
                    else
                    {
                        cityModel = new ProSurveyStatisModel();
                        cityModel.FOrchard = 0;
                        cityModel.FTOrchard = 0;
                        cityModel.GrandArea = 0;
                        cityModel.NewArea = 0;
                        cityModel.TOrchard = 0;
                        cityModel.CumYield = 0;
                    }
                    cityModel.AreaName = "合计";
                    list.Add(cityModel);
                }
                else
                {
                    list = getCountyStatisList(AreaCode, FillInYear, VarietyCode);
                }
            }
            obj.model = list;
            obj.status = DefaultKey.SuccessResult;
            obj.message = "获取成功";
            return obj;
        }
        public ProSurveyStatisModel getProvinStatis(string AreaCode, string FillInYear, string VarietyCode)
        {
            ProSurveyStatisModel list = new ProSurveyStatisModel();
            string strSql = "select t3.Variety,";
            strSql += "isnull(sum(t3.GrandArea),0.00) GrandArea ,";
            strSql += "isnull(sum(t3.NewArea),0.00) NewArea ,";
            strSql += "isnull(sum(t3.FOrchard),0.00) FOrchard ,";
            strSql += "isnull(sum(t3.FTOrchard),0.00) FTOrchard ,";
            strSql += "isnull(sum(t3.TOrchard),0.00) TOrchard,";
            //strSql += "isnull(sum(t3.AvgMuYield),0.00) AvgMuYield ,";
            strSql += "isnull(sum(t3.CumYield),0.00) CumYield ";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code ";
            strSql += "left join  T_FruProSurvey t3 on t2.Id = t3.ReportId where t2.ReportCode = 'FruProSurvey' and t1.ParentCode = @AreaCode and t2.FillInYear = @FillInYear ";
            strSql += "  and t2.Status = '4' ";
            if (VarietyCode != null && VarietyCode != "")
            {
                strSql += " and VarietyCode = '" + VarietyCode + "' ";
            }
            strSql += " group by t3.Variety,t3.SortNum order by t3.SortNum";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProSurveyStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault();
                    if(list.GrandArea != 0||list.CumYield != null)
                    {
                        list.AvgMuYield = Decimal.Round((decimal)(list.CumYield / list.GrandArea), 1);
                    }
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取主要水果年度面积产量统计列表信息失败", ex);
            }
            return list;
        }
        public List<ProSurveyStatisModel> getCityStatisList(string AreaCode, string FillInYear, string VarietyCode)
        {
            List<ProSurveyStatisModel> list = new List<ProSurveyStatisModel>();
            string strSql = "select t1.Name AreaName,t3.Variety,";
            strSql += "isnull(t3.GrandArea,0.00) GrandArea ,";
            strSql += "isnull(t3.NewArea,0.00) NewArea ,";
            strSql += "isnull(t3.FOrchard,0.00) FOrchard ,";
            strSql += "isnull(t3.FTOrchard,0.00) FTOrchard ,";
            strSql += "isnull(t3.TOrchard,0.00) TOrchard,";
           strSql += "isnull(t3.AvgMuYield,0.00) AvgMuYield ,";
            strSql += "isnull(t3.CumYield,0.00) CumYield ";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code ";
            strSql += "left join  T_FruProSurvey t3 on t2.Id = t3.ReportId where t2.ReportCode = 'FruProSurvey' and t1.ParentCode = @AreaCode and t2.FillInYear = @FillInYear ";
            strSql += "  and t2.Status = '4' ";
            if (VarietyCode != null && VarietyCode != "")
            {
                strSql += " and (t3.VarietyCode = '" + VarietyCode + "' or t3.VarietyCode is null)";
            }
            strSql += "order by t1.Code,t3.SortNum";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProSurveyStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).ToList();                   
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取主要水果年度面积产量统计列表信息失败", ex);
            }
            return list;
        }
        public List<ProSurveyStatisModel> getCountyStatisList(string AreaCode, string FillInYear, string VarietyCode)
        {
            List<ProSurveyStatisModel> list = new List<ProSurveyStatisModel>();
            string strSql = "select t1.Name AreaName,t3.Variety,";
            strSql += "t3.GrandArea GrandArea ,";
            strSql += "t3.NewArea NewArea ,";
            strSql += "t3.FOrchard FOrchard ,";
            strSql += "t3.FTOrchard FTOrchard ,";
            strSql += "t3.TOrchard TOrchard,";
            strSql += "t3.AvgMuYield AvgMuYield ,";
            strSql += "t3.CumYield CumYield ";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code ";
            strSql += "left join  T_FruProSurvey t3 on t2.Id = t3.ReportId where t2.ReportCode = 'FruProSurvey' and t1.Code = @AreaCode and t2.FillInYear = @FillInYear ";
            strSql += "  and t2.Status = '4' ";
            if (VarietyCode != null && VarietyCode != "")
            {
                strSql += " and (t3.VarietyCode = '" + VarietyCode + "' or t3.VarietyCode is null)";
            }
            strSql += "order by t1.Code,t3.SortNum";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProSurveyStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).ToList();                    
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取主要水果年度面积产量统计列表信息失败", ex);
            }
            return list;
        }

        /// <summary>
        /// 获取全省的各种类的数据
        /// </summary>
        /// <param name="FillInYear"></param>
        /// <param name="VarietyCode"></param>
        /// <returns></returns>
        public ProSurveyStatisModel getTProvinStatis(string FillInYear, string VarietyCode)
        {
            ProSurveyStatisModel list = new ProSurveyStatisModel();
            string strSql = "select t3.Variety,";
            strSql += "isnull(sum(t3.GrandArea),0.00) GrandArea ,";
            strSql += "isnull(sum(t3.NewArea),0.00) NewArea ,";
            strSql += "isnull(sum(t3.FOrchard),0.00) FOrchard ,";
            strSql += "isnull(sum(t3.FTOrchard),0.00) FTOrchard ,";
            strSql += "isnull(sum(t3.TOrchard),0.00) TOrchard,";
          //  strSql += "isnull(sum(t3.AvgMuYield),0.00) AvgMuYield ,";
            strSql += "isnull(sum(t3.CumYield),0.00) CumYield ";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code ";
            strSql += "left join  T_FruProSurvey t3 on t2.Id = t3.ReportId where t2.ReportCode = 'FruProSurvey' and t1.ParentCode like @AreaCode and t2.FillInYear = @FillInYear ";
            strSql += "  and t2.Status = '4' ";
            if (VarietyCode != null && VarietyCode != "")
            {
                strSql += " and VarietyCode = '" + VarietyCode + "' ";
            }
            strSql += " group by t3.Variety,t3.SortNum order by t3.SortNum";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProSurveyStatisModel>(strSql, new { AreaCode = "%53%", FillInYear = FillInYear }).FirstOrDefault();
                    if (list.GrandArea != 0 && list.CumYield != null)
                    {
                        list.AvgMuYield = Decimal.Round((decimal)(list.CumYield / list.GrandArea),1);
                    }
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取主要水果年度面积产量统计列表信息失败", ex);
            }
            return list;
        }

        public List<ProSurveyStatisModel> getAllCountyStatisList(string FillInYear, string VarietyCode)
        {
            List<ProSurveyStatisModel> list = new List<ProSurveyStatisModel>();
            string strSql = "select t1.Name AreaName,t3.Variety,";
            strSql += "isnull(t3.GrandArea,0.00) GrandArea ,";
            strSql += "isnull(t3.NewArea,0.00) NewArea ,";
            strSql += "isnull(t3.FOrchard,0.00) FOrchard ,";
            strSql += "isnull(t3.FTOrchard,0.00) FTOrchard ,";
            strSql += "isnull(t3.TOrchard,0.00) TOrchard,";
            strSql += "isnull(t3.AvgMuYield,0.00) AvgMuYield ,";
            strSql += "isnull(t3.CumYield,0.00) CumYield ";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code ";
            strSql += "left join  T_FruProSurvey t3 on t2.Id = t3.ReportId where t2.ReportCode = 'FruProSurvey' and t1.ParentCode != '530000000000' and t2.FillInYear = @FillInYear ";
            strSql += " and t1.ParentCode is not null ";
            strSql += "  and t2.Status = '4' ";
            if (VarietyCode != null && VarietyCode != "")
            {
                strSql += " and (t3.VarietyCode = '" + VarietyCode + "' or t3.VarietyCode is null)";
            }
            strSql += "order by t1.Code ,t3.SortNum";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<ProSurveyStatisModel>(strSql, new { FillInYear = FillInYear }).ToList();                   
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取主要水果年度面积产量统计列表信息失败", ex);
            }
            return list;
        }
        /// <summary>
        /// 导出的汇总
        /// </summary>
        /// <param name="AreaCode"></param>
        /// <param name="FillInYear"></param>
        /// <param name="VarietyCode"></param>
        /// <returns></returns>
        public ResResult<List<ProSurveyStatisModel>> getOutStatisList(string AreaCode, string FillInYear, string VarietyCode)
        {
            ResResult<List<ProSurveyStatisModel>> obj = new ResResult<List<ProSurveyStatisModel>>();
            List<ProSurveyStatisModel> list = new List<ProSurveyStatisModel>();
            AreaServices areaServices = new AreaServices();
            if (AreaCode.Equals("530000000000"))
            {
                var citylist = areaServices.GetPrefectureList();
                ProSurveyStatisModel totalModel = new ProSurveyStatisModel();
                totalModel.AreaName = "合计";
                totalModel.FOrchard = 0;
                totalModel.FTOrchard = 0;
                totalModel.GrandArea = 0;
                totalModel.NewArea = 0;
                totalModel.TOrchard = 0;
                totalModel.CumYield = 0;
                totalModel.tag = 2;
                foreach (var code in citylist)
                {
                    list.AddRange(getCityStatisList(code, FillInYear, VarietyCode));
                    ProSurveyStatisModel cityModel = getProvinStatis(code, FillInYear, VarietyCode);
                    if (cityModel != null)
                    {
                        cityModel.AreaName = areaServices.GetByCodeModel(code).model.Name;
                        if (cityModel.GrandArea != 0 && cityModel.CumYield != null)
                        {
                            cityModel.AvgMuYield = Decimal.Round((decimal)(cityModel.CumYield / cityModel.GrandArea), 1);
                        }
                        totalModel.FOrchard += cityModel.FOrchard;
                        totalModel.FTOrchard += cityModel.FTOrchard;
                        totalModel.GrandArea += cityModel.GrandArea;
                        totalModel.NewArea += cityModel.NewArea;
                        totalModel.TOrchard += cityModel.TOrchard;
                        totalModel.CumYield += cityModel.CumYield;
                    }
                    else
                    {
                        cityModel = new ProSurveyStatisModel();
                        cityModel.AreaName = areaServices.GetByCodeModel(code).model.Name;
                        cityModel.FOrchard = 0;
                        cityModel.FTOrchard = 0;
                        cityModel.GrandArea = 0;
                        cityModel.NewArea = 0;
                        cityModel.TOrchard = 0;
                        cityModel.CumYield = 0;
                    }
                    cityModel.tag = 1;
                    list.Add(cityModel);

                }
                if (totalModel.GrandArea != 0 && totalModel.CumYield != null)
                {
                    totalModel.AvgMuYield = Decimal.Round((decimal)(totalModel.CumYield / totalModel.GrandArea), 1);
                }
                list.Add(totalModel);
            }
            else
            {
                if (AreaCode.Substring(4, 8).Equals("00000000"))
                {
                    list = getCityStatisList(AreaCode, FillInYear, VarietyCode);
                    ProSurveyStatisModel cityModel = getProvinStatis(AreaCode, FillInYear, VarietyCode);
                    if (cityModel != null)
                    {
                        if (cityModel.GrandArea != 0 && cityModel.CumYield != null)
                        {
                            cityModel.AvgMuYield = Decimal.Round((decimal)(cityModel.CumYield / cityModel.GrandArea), 1);
                        }
                    }
                    else
                    {
                        cityModel = new ProSurveyStatisModel();
                        cityModel.FOrchard = 0;
                        cityModel.FTOrchard = 0;
                        cityModel.GrandArea = 0;
                        cityModel.NewArea = 0;
                        cityModel.TOrchard = 0;
                        cityModel.CumYield = 0;
                    }
                    cityModel.AreaName = "合计";
                    list.Add(cityModel);
                }
                else
                {
                    list = getCountyStatisList(AreaCode, FillInYear, VarietyCode);
                }
            }
            obj.model = list;
            obj.status = DefaultKey.SuccessResult;
            obj.message = "获取成功";
            return obj;
        }

    }
}